|
 |
 |
To access the contents, click the chapter and section titles.
Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96
Data Access Patterns
For individual transactions, the data access patterns in a DSS system are fairly straightforward. Based on the types of transactions you are generating, you should be able to fairly accurately determine these patterns. Although each system has its own specific data access patterns, a decision support system has the following general characteristics.
- Data access to the redo logs is either minimal or none. If the system is purely used for DSS, there are usually no updates while DSS queries are running.
- No archiving. Archiving is not necessary because of the lack of activity on the redo logs.
- Data access for each query is mostly sequential. Because the nature of the queries is usually to extract large amounts of data from the tables, full-table scans are not uncommon.
- Data reads can (and frequently do) take advantage of multiblock reads. You can expect that many of the disk accesses are the size of multiblock reads.
- Data access to the data files is somewhat random. This random access is primarily caused by contention with other users and because join and index operations result in fairly random access across the data volumes. However, these random reads from the disk drives access the data with a much larger disk request.
- Heavy access to the temporary tables. This heavy access pattern is caused by the typical size of many of the join and sort operations. Remember: Only sorts that use less memory than SORT_AREA_SIZE are in memory.
- Fairly even access to all data. Although some data may be accessed more than others, this is usually on a table-by-table basis. Most data within the tables is accessed somewhat uniformly.
These patterns vary depending on how your system operates, but the general principles are the same. The access patterns to your tables vary based on how often the table is accessed and how much is done to each table.
System Load
In the DSS system, the CPUs usually are 100 percent active during the DSS queries. Unlike OLTP systems, which have many users with small queries, a DSS system has relatively few users and massive queries. These queries should be able to take advantage of the full capabilities of the CPUs and memory (as long as the system does not become disk bound). By following the guidelines described in Chapters 9 and 10, you can make the system CPU bound.
In the real world, I see systems that show a significant I/O deficiency. If you have an I/O capacity that does not fit the system load, you will most likely see a significant number of CPU idle cycles while the processes are waiting for I/Os to complete. Waiting for I/Os to complete can severely degrade performance.
The load characteristics of a DSS system include the following items:
- A relatively few number of processes on the system. This is true unless you take advantage of the Parallel Query option, which adds more processes and subsequently more process switches.
- Minimal network traffic. Typically, there is very little network traffic during the time that the DSS queries are running.
- Heavy I/O usage. DSS systems usually generate large amounts of I/O to the data files. This I/O is somewhat random if multiple DSS queries are active simultaneously but the I/Os are larger in size because of multiblock reads.
- Very little or no redo log usage. Because DSS functions typically do not do updates, the redo log is idle.
- Very little or no use of rollback segments. Again, because DSS functions consist of very little update activity, the rollback segments are hardly used.
- Moderate amounts of memory. The memory is used not only for the SGA but for each of the server processes for sort and join operations.
You can use these characteristics to help design and tune your DSS system for optimal performance. The first step in this design process is to set goals for what you want to achieve.
Goals
The goal in tuning the DSS system is to achieve a system that has certain characteristics. Here are the goals of an optimally tuned DSS system:
- The system is CPU bound. If you remove all other bottlenecks, the system should be able to process as fast as it can, which means at the speed of its CPUs.
- The system does not show signs of being drive bound. Any disk bottlenecks degrade performance. If the system is disk bound, you should either add more disks or increase memory.
- Memory is sufficient. If the machine pages or swaps, performance is severely degraded. The best solution is to add more memory; if that is not possible, reduce the size of the SGA or the number of users until the system no longer pages or swaps.
- The system must meet any additional requirements you have. With some DSS machines, it is necessary to keep current with the OLTP systems on a nightly basis. If this is a criterion of your system, you must make sure that you can upload new data within the specified time.
By setting goals for how you expect the system to perform, you can determine whether your tuning efforts are successful. You can also determine earlier whether you will be able to achieve the specified goals.
Review of DSS System Characteristics
By analyzing the characteristics of the DSS system along with the goals you want to achieve, you can obtain a lot of data with which you can build the system. Much of the design of the system is determined by how data is accessed and how many and what type of queries are specified.
The data access in a DSS system is generally of a random nature, although it does have sequential components. By configuring the system to take advantage of multiblock reads, you can achieve a high level of I/O performance.
Relate the information in the next part of this chapter to your particular configuration. Look for similarities and differences between what is described here and what you have observed about your system and decide how you can benefit from the tuning guidelines presented here. Although each system is different, many of the concepts remain the same.
You should base your system design on what you know about your application and system. Spend the time up front to carefully examine how the system needs to run so that you can determine the design of the system. The quality of the end product depends heavily on the amount of effort you put in at the beginning stages. No amount of tuning after the system is in production can make up for poor design choices. |
|